package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Log;
import model.User;
import database.DBManager;


public class LogDao {
	
	private String sqlGetU   	   = "SELECT * FROM user WHERE userID = ?";
	private String sqlReadP		   = "SELECT log.userID, log.date, log.pageID, log.ip, page.path FROM inti3e.log INNER JOIN inti3e.page ON log.pageID=page.pageID ORDER BY log.ip DESC LIMIT 15";
	private String sqlCreate   	   = "INSERT INTO log(userID, date, pageID, ip) VALUES(?, NOW(), ?, ?)";
	private String sqlReadP10	   = "SELECT log.userID, log.date, log.pageID, log.ip, page.path FROM inti3e.log INNER JOIN inti3e.page ON log.pageID=page.pageID GROUP BY log.pageID ORDER BY log.pageID DESC LIMIT 10";

	
	private Connection        con      = null;
	private PreparedStatement psGetU   = null; 
	private PreparedStatement psReadP  = null;
	private PreparedStatement psReadP10  = null;
	private PreparedStatement psCreate = null;

	public LogDao(DBManager myDb){
		setStatements(myDb);
	}
	
	private void setStatements(DBManager myDb){
		con = myDb.getConnection();
		try{
			
			this.psGetU  	  = con.prepareStatement(sqlGetU);
			this.psCreate 	  = con.prepareStatement(sqlCreate);
			this.psReadP 	  = con.prepareStatement(sqlReadP);
			this.psReadP10 	  = con.prepareStatement(sqlReadP10);

		} catch(SQLException se) {
			se.printStackTrace();
		}
	}
	
	public ArrayList<Log> getLogsWithPagePath(){
		ArrayList<Log> logs = new ArrayList<Log>();
		try{
			ResultSet rs = psReadP.executeQuery();
			while (rs.next()) {
				int userID = rs.getInt(1);
				Date date = rs.getDate(2);
				int page = rs.getInt(3);
				String ipadress = rs.getString(4);
				String path = rs.getString(5);
				

				User user = getUserByID(userID); 
				int count = 0;
				Log log = new Log(user, date, page, ipadress, path, count);
				logs.add(log);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return logs;
	}	
	public boolean createLog(int userID, int pageID, String IPaddress) {
		boolean succeeded = false;
		try {
			psCreate.clearParameters();
			psCreate.setInt(1, userID);
			psCreate.setInt(2, pageID);
			psCreate.setString(3, IPaddress);
			System.out.println(psCreate);
			if( psCreate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	public ArrayList<Log> getLogsTop10i() {
		ArrayList<Log> logs = new ArrayList<Log>();
		try{
			psReadP10.clearParameters();
			psReadP10 	  = con.prepareStatement("SELECT DISTINCT log.userID, log.pageID, log.ip, COUNT(*) FROM inti3e.log INNER JOIN inti3e.page ON log.pageID=page.pageID GROUP BY log.ip ORDER BY log.ip DESC LIMIT 10");
			ResultSet rs = psReadP10.executeQuery();	
			while (rs.next()) {
				int userID = rs.getInt(1);
				int page = rs.getInt(2);
				String ipadress = rs.getString(3);
				int count = rs.getInt(4);
				
				User user = getUserByID(userID); 
				
				Log log = new Log(user, page, ipadress, count);
				logs.add(log);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return logs;
	}	
	public ArrayList<Log> getLogsTop10u() {
		ArrayList<Log> logs = new ArrayList<Log>();
		try{
			psReadP10.clearParameters();
			psReadP10 	  = con.prepareStatement("SELECT log.userID, log.date, log.pageID, log.ip, COUNT(*) FROM inti3e.log INNER JOIN inti3e.page ON log.pageID=page.pageID GROUP BY log.userID ORDER BY log.userID DESC LIMIT 10");
			ResultSet rs = psReadP10.executeQuery();	
			while (rs.next()) {
				int userID = rs.getInt(1);
				Date date = rs.getDate(2);
				int page = rs.getInt(3);
				String ipadress = rs.getString(4);
				int count = rs.getInt(5);
				
				User user = getUserByID(userID); 
				
				Log log = new Log(user, date, page, ipadress, count);
				logs.add(log);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return logs;
	}	
	public ArrayList<Log> getLogsTop10p() {
		ArrayList<Log> logs = new ArrayList<Log>();
		try{
			psReadP10.clearParameters();
			
			psReadP10 	  = con.prepareStatement("SELECT DISTINCT log.date, log.pageID, log.ip, page.path, COUNT(*) FROM inti3e.log INNER JOIN inti3e.page ON log.pageID=page.pageID GROUP BY log.pageID ORDER BY page.path DESC LIMIT 10");	
			ResultSet rs = psReadP10.executeQuery();	
			while (rs.next()) {
				Date date = rs.getDate(1);
				int page = rs.getInt(2);
				String ipadress = rs.getString(3);
				String path = rs.getString(4);
				int count = rs.getInt(5);
				 
				
				Log log = new Log(date, page, ipadress, path, count);
				logs.add(log);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return logs;
	}	
	public ArrayList<Log> getLogsTop10d() {
		ArrayList<Log> logs = new ArrayList<Log>();
		try{
			psReadP10.clearParameters();
			
			psReadP10 	  = con.prepareStatement("SELECT log.date, COUNT(*) FROM inti3e.log GROUP BY log.date LIMIT 10");	
			ResultSet rs = psReadP10.executeQuery();	
			while (rs.next()) {
				Date date = rs.getDate(1);
				int count = rs.getInt(2);
				
				Log log = new Log(date, count);
				logs.add(log);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return logs;
	}	
	
	public User getUserByID(int getUserID){
		User user = null;
		try{
			psGetU.clearParameters() ;
			psGetU.setInt(1, getUserID);
			ResultSet rs = psGetU.executeQuery();
			while (rs.next()) {
				int userID = rs.getInt(1);
				String username = rs.getString(2);
				String password = rs.getString(3);
				int rolID = rs.getInt(4);
				
				user = new User(userID, username, password, rolID);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return user;
	}

}
